﻿using Ayatta.Contract.Component;
using Ayatta.Service.Extension;
using Dapper;
using System;
using System.Data;
using System.Text;
using System.Linq;
using Ayatta.Contract;
using System.Xml.Linq;
using Ayatta.Contract.Domain;
using System.Collections.Generic;
using Ayatta.Common.Extensions;

namespace Ayatta.Service
{
    public class PassportService : ServiceBase, IPassportService
    {
        #region User

        public int CreateUser(User o)
        {
            var xml = string.Empty;
            var xElement = new XElement("xml");
            try
            {
                xElement.Add(new XElement("user",
                                          new XAttribute("Id", o.Id),
                                          new XAttribute("Guid", o.Guid),
                                          new XAttribute("Name", o.Name),
                                          new XAttribute("NickName", o.NickName),
                                          new XAttribute("Password", o.Password),
                                          new XAttribute("PayPassword", o.PayPassword),
                                          new XAttribute("Role", (byte)o.Role),
                                          new XAttribute("Level", (byte)o.Level),
                                          new XAttribute("Limit", (int)o.Limit),
                                          new XAttribute("Permission", (int)o.Permission),
                                          new XAttribute("Email", o.Email),
                                          new XAttribute("Mobile", o.Mobile),
                                          new XAttribute("Status", o.Status ? 1 : 0)
                                 ));
                var profile = o.Profile;
                xElement.Add(new XElement("profile",
                                           new XAttribute("Id", o.Id),
                                           new XAttribute("NationalId", profile.NationalId),
                                           new XAttribute("RealName", profile.RealName),
                                           new XAttribute("Gender", (byte)profile.Gender),
                                           new XAttribute("Marital", (byte)profile.Marital),
                                           new XAttribute("Birthday", profile.Birthday.HasValue ? profile.Birthday.Value.ToString("yyyy-MM-dd") : "null"),
                                           new XAttribute("Phone", profile.Phone),
                                           new XAttribute("Mobile", profile.Mobile),
                                           new XAttribute("AreaId", profile.AreaId),
                                           new XAttribute("Address", profile.Address),
                                           new XAttribute("Avatar", profile.Avatar),
                                           new XAttribute("RegIp", profile.RegIp),
                                           new XAttribute("RegBy", profile.RegBy),
                                           new XAttribute("TraceCode", profile.TraceCode),
                                           new XAttribute("CreatedBy", profile.CreatedBy)
                                  ));

                xml = xElement.ToString();
                var param = new DynamicParameters();
                param.Add("@xml", xml, DbType.Xml);
                param.Add("@id", 0, direction: ParameterDirection.Output);
                using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
                {
                    connection.Execute("[Proc.User.Reg]", param, commandType: CommandType.StoredProcedure);
                    return param.Get<int>("@id");
                }
            }
            catch (Exception)
            {
                return 0;
            }
        }

        public bool UpdateUserNickName(int id, string nickName)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Field("[NickName]=@NickName", new { nickName })
                .Where("[Id] = @Id", new { id });
            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateUserAvatar(int id, string avatarUrl)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserProfile)
                .Field("[Avatar]=@Avatar", new { avatar = avatarUrl })
                .Where("[Id] = @Id", new { id });
            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteUser(int id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public User GetUser(int id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Passport.Table.User)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<User>(connection, template).FirstOrDefault();
            }
        }

        public PagedList<User> GetPagedUsers(int index = 1, int size = 20, int? id = null, string guid = null, string name = null, bool? status = null)
        {
            var builder = new SqlBuilder().Field("*")
               .Table(Database.Passport.Table.User)
               .OrderBy("[Id] DESC")
               .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
            if (id.HasValue && id.Value > 0)
            {
                builder.Where("[Id]=@Id", new { id });
            }
            if (!string.IsNullOrEmpty(guid))
            {
                builder.Where("[Guid] = @Guid", new { guid });
            }
            if (!string.IsNullOrEmpty(name))
            {
                builder.Where("[Name] LIKE @Name", new { name = name + "%" });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }
            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return connection.PagedQuery<User>(template);
            }
        }

        public User GetUser(int id, bool inclProfile = false, bool inclExtra = false)
        {
            User o;
            const string tpl = "SELECT TOP 1 * FROM {0} WHERE [Id]=@Id;";
            var sb = new StringBuilder();
            sb.AppendFormat(tpl, Database.Passport.Table.User);
            if (inclProfile)
            {
                sb.AppendFormat(tpl, Database.Passport.Table.UserProfile);
            }
            if (inclExtra)
            {
                sb.AppendFormat(tpl, Database.Passport.Table.UserExtra);
            }

            var template = new SqlBuilder().AddTemplate(sb.ToString(), new { id });
            using (var connection = GetConnection(ConnectionName.Passport))
            using (var multi = connection.QueryMultiple(template))
            {
                o = multi.Read<User>().FirstOrDefault();
                if (o != null)
                {
                    if (inclProfile)
                    {
                        o.Profile = multi.Read<UserProfile>().FirstOrDefault();
                    }
                    if (inclExtra)
                    {
                        o.Extra = multi.Read<UserExtra>().FirstOrDefault();
                    }
                }
            }
            return o;
        }

        public User GetUser(string username)
        {
            var builder = new SqlBuilder()
                .Field("*").Table(Database.Passport.Table.User)
                .Where("[Name]=@Name", new { name = username });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<User>(connection, template).FirstOrDefault();
            }
        }

        public User GetUser(string name, string password)
        {
            var sb = new StringBuilder();

            sb.AppendLine(
                "if exists(select 0 from [User] a where a.[Name]=@Name and a.[Password]=@Password) begin select top 1 * from [User] a where a.[Name]=@Name and a.[Password]=@Password end");

            if (name.IsMobile())
            {
                sb.AppendLine(
                    "else if exists(select 0 from [User] a where a.[Mobile] is not null and a.[Mobile]=@Name and a.[Password]=@Password) begin select top 1 * from [User] a where a.[Mobile] is not null and a.[Mobile]=@Name and a.[Password]=@Password end");
            }
            else if (name.IsEmail())
            {
                sb.AppendLine(
                    "else if exists(select 0 from [User] a where a.[Email] is not null and a.[Email]=@Name and a.[Password]=@Password) begin select top 1 * from [User] a where a.[Email] is not null and a.[Email]=@Name and a.[Password]=@Password end");
            }

            sb.AppendLine("else begin select top 0 * from [User] where 1<>1 end");

            var template = new SqlBuilder().AddParameters(new { name, password }).AddTemplate(sb.ToString());
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<User>(connection, template).FirstOrDefault();
            }
        }

        public User GetUserByMobile(string mobile)
        {
            var builder = new SqlBuilder()
                .Field("*").Table(Database.Passport.Table.User)
                .Where("[Mobile]=@Mobile", new { mobile });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<User>(connection, template).FirstOrDefault();
            }
        }

        public bool ExistUserGuid(Guid guid)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Where("[Guid]=@Guid", new { guid });

            var template = builder.AddTemplate(SqlTemplate.Count);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<int>(connection, template).First() > 0;
            }

        }

        public bool ExistUserName(string username)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Where("[Name]=@Name", new { name = username });

            var template = builder.AddTemplate(SqlTemplate.Count);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<int>(connection, template).First() > 0;
            }
        }

        public bool ExistBindingEmail(string email)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Where("[Email]=@Email", new { email });

            var template = builder.AddTemplate(SqlTemplate.Count);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<int>(connection, template).First() > 0;
            }
        }

        public bool ExistBindingMobile(string mobile)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Where("[Mobile]=@Mobile", new { mobile });

            var template = builder.AddTemplate(SqlTemplate.Count);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<int>(connection, template).First() > 0;
            }
        }

        public bool EmailBinding(int userId, string email)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Field("[Email]=@Email", new { email })
                .Where("[Id]=@Id", new { Id = userId });
            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) != 0;
            }
        }

        public bool MobileBinding(int userId, string mobile)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Field("[Mobile]=@Mobile", new { mobile })
                .Where("[Id]=@Id", new { Id = userId });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) != 0;
            }
        }

        public bool ChangePassword(int userId, string password)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Field("[Password]=@Password", new { password })
                .Where("[Id]=@UserId", new { userId });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) != 0;
            }
        }

        public UserProfile GetUserProfile(int id)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Passport.Table.UserProfile)
                .Where("[Id]=@Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<UserProfile>(connection, template).FirstOrDefault();
            }
        }

        public bool UpdateUserProfile(UserProfile o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserProfile)
                .Field("[Marital]=@Marital", new { o.Marital })
                .Field("[Phone]=@Phone", new { o.Phone })
                .Field("[Mobile]=@Mobile", new { o.Mobile })
                .Field("[Avatar]=@Avatar", new { o.Avatar })
                .Field("[AreaId]=@AreaId", new { o.AreaId })
                .Field("[Address]=@Address", new { o.Address })
                .Where("[Id]=@Id", new { o.Id });
            if (!o.Authenticated)
            {
                builder.Field("[NationalId]=@NationalId", new { o.NationalId })
                .Field("[RealName]=@RealName", new { o.RealName })
                .Field("[Birthday]=@Birthday", new { o.Birthday })
                .Field("[Gender]=@Gender", new { o.Gender });
            }

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public void UpdateUserLastLoginInfo(int id, string ip)
        {
            var builder = new SqlBuilder()
               .Table(Database.Passport.Table.UserExtra)
               .Field("[LastLoginIp]=@ip,[LastLoginTime]=getdate()", new { ip })
               .Where("[Id]=@Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                Execute(connection, template);
            }
        }

        public OAuthConfig GetOAuthConfig(int id)
        {
            var builder = new SqlBuilder().Field("*")
            .Table(Database.Passport.Table.OAuthConfig)
            .Where("[Id]=@Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<OAuthConfig>(connection, template).FirstOrDefault();
            }
        }

        public IList<OAuthConfig> GetOAuthConfigs(bool? status = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Passport.Table.OAuthConfig);
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<OAuthConfig>(connection, template).ToList();
            }
        }

        public void ExternalLogin(ExternalUser o)
        {
            var param = new { o.Guid, o.Name, o.OpenId, o.Scope, o.AccessToken, o.RefreshToken, o.ExpirationTime, o.Extra, o.CreatedBy, o.CreatedOn };
            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                connection.Execute("[Proc.User.External.Login]", param, commandType: CommandType.StoredProcedure);
            }
        }

        public UserExtra GetUserExtra(int id)
        {
            var builder = new SqlBuilder().Field("*")
            .Table(Database.Passport.Table.UserExtra)
            .Where("[Id]=@Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<UserExtra>(connection, template).FirstOrDefault();
            }
        }
        #endregion

        #region UserAddress

        public UserAddress GetUserAddress(int id, int? userId = null)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Passport.Table.UserAddress)
                .Where("[Id]=@Id", new { id });
            if (userId.HasValue)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }
            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<UserAddress>(connection, template).FirstOrDefault();
            }
        }

        public IList<UserAddress> GetUserAddresses(int userId)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Passport.Table.UserAddress)
                .Where("[UserId]=@UserId", new { userId });

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<UserAddress>(connection, template).ToList();
            }
        }

        public bool CreateUserAddress(UserAddress o)
        {
            const string field =
                @"([UserId],[Category],[Name],[Company],[CountryId],[AreaId],[Province],[City],[District],[Address],[PostalCode],[Phone],[Mobile],[IsDefault],[CreatedBy],[CreatedOn])
                VALUES(@UserId,@Category,@Name,@Company,@CountryId,@AreaId,@Province,@City,@District,@Address,@PostalCode,@Phone,@Mobile,@IsDefault,@CreatedBy,@CreatedOn)";
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserAddress)
                .Field(field)
                .AddParameters(new { o.UserId, o.Category, o.Name, o.Company, o.CountryId, o.AreaId, o.Province, o.City, o.District, o.Address, o.PostalCode, o.Phone, o.Mobile, o.IsDefault, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateUserAddress(UserAddress o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserAddress)
                .Field("[Name]=@Name", new { o.Name })
                .Field("[Category]=@Category", new { o.Category })
                .Field("[Company]=@Company", new { o.Company })
                .Field("[CountryId]=@CountryId", new { o.CountryId })
                .Field("[AreaId]=@AreaId", new { o.AreaId })
                .Field("[Province]=@Province", new { o.Province })
                .Field("[City]=@City", new { o.City })
                .Field("[District]=@District", new { o.District })
                .Field("[Phone]=@Phone", new { o.Phone })
                .Field("[Mobile]=@Mobile", new { o.Mobile })
                .Field("[Address]=@Address", new { o.Address })
                .Field("[PostalCode]=@PostalCode", new { o.PostalCode })
                .Field("[IsDefault]=@IsDefault", new { o.IsDefault })
                .Where("[UserId]=@UserId AND [Id]=@Id", new { o.Id, o.UserId });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteUserAddress(int id, int? userId = null)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserAddress)
                .Where("[Id]=@Id", new { id });
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) >= 1;
            }
        }

        public bool SetDefaultUserAddress(int id, int userId, AddressCategory category)
        {
            var builder = new SqlBuilder().AddParameters(new { id, userId, category });

            var sb = new StringBuilder();
            sb.AppendFormat("update {0} set [IsDefault]=0 where [UserId]=@UserId AND [Category]=@Category AND [Id]<>@Id;",
                            Database.Passport.Table.UserAddress);
            sb.AppendFormat("update {0} set [IsDefault]=1 where [UserId]=@UserId AND [Category]=@Category AND [Id]=@Id;",
                            Database.Passport.Table.UserAddress);

            var template = builder.AddTemplate(sb.ToString());
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) >= 1;
            }
        }


        #endregion


        #region UserInvoice

        public UserInvoice GetUserInvoice(int id, int? userId = null)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Passport.Table.UserInvoice)
                .Where("[Id]=@Id", new { id });
            if (userId.HasValue)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }
            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<UserInvoice>(connection, template).FirstOrDefault();
            }
        }

        public IList<UserInvoice> GetUserInvoices(int userId)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Passport.Table.UserInvoice)
                .Where("[UserId]=@UserId", new { userId });

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<UserInvoice>(connection, template).ToList();
            }
        }

        public bool CreateUserInvoice(UserInvoice o)
        {
            const string field =
                @"([UserId],[Category],[Title],[Content],[IsDefault],[CreatedBy],[CreatedOn])
                VALUES(@UserId,@Category,@Title,@Content,@IsDefault,@CreatedBy,@CreatedOn)";
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserInvoice)
                .Field(field)
                .AddParameters(new { o.UserId, o.Category, o.Title, o.Content, o.IsDefault, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateUserInvoice(UserInvoice o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserInvoice)
                .Field("[Category]=@Category", new { o.Category })
                .Field("[Title]=@Title", new { o.Title })
                .Field("[Content]=@Content", new { o.Content })
                .Field("[IsDefault]=@IsDefault", new { o.IsDefault })
                .Where("[UserId]=@UserId AND [Id]=@Id", new { o.Id, o.UserId });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteUserInvoice(int id, int? userId = null)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserInvoice)
                .Where("[Id]=@Id", new { id });
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool SetDefaultUserInvoice(int id, int userId)
        {
            var builder = new SqlBuilder().AddParameters(new { id, userId });

            var sb = new StringBuilder();
            sb.AppendFormat("update {0} set [IsDefault]=0 where [UserId]=@UserId AND [Id]<>@Id;",
                            Database.Passport.Table.UserInvoice);
            sb.AppendFormat("update {0} set [IsDefault]=1 where [UserId]=@UserId AND [Id]=@Id;",
                            Database.Passport.Table.UserInvoice);

            var template = builder.AddTemplate(sb.ToString());
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }


        #endregion

        #region Implementation of IService

        public int GetCachedSqlCount()
        {
            return SqlMapper.GetCachedSQLCount();
        }

        public List<Tuple<string, string, int>> GetCachedSql()
        {
            return SqlMapper.GetCachedSQL().ToList();
        }

        public void PurgeQueryCache()
        {
            SqlMapper.PurgeQueryCache();
        }

        #endregion

    }
}